package com.kingre.et.dao.jpa;

import java.util.List;

import com.kingre.et.Constants;
import com.kingre.et.dao.IUserDAO;
import com.kingre.et.model.School;
import com.kingre.et.model.User;

public class UserDAOJPA extends GenericDAOJPA<User, Integer> implements IUserDAO {
    @Override
    @SuppressWarnings("unchecked")
    public User findByUsername(String username) {
        List<User> users = getJpaTemplate().find("FROM User p WHERE username = ?", username);

        if (!users.isEmpty()) {
            return users.get(0);
        }
        return null;
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<User> findByNameAndSchool(String roleName, School school, String name) {
        List<User> users = getJpaTemplate()
                .find("SELECT DISTINCT p FROM User p, IN(p.role) o WHERE o.name = ? AND p.school = ? AND p.name like ? AND p.statusCode = 1 ORDER BY p.name",
                        roleName, school, "%" + name + "%");
        return users;
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<User> findByName(String roleName, String name) {
        List<User> users = getJpaTemplate()
                .find("SELECT DISTINCT p FROM User p, IN(p.role) o WHERE o.name = ? AND p.name like ? AND p.statusCode = 1 ORDER BY p.name",
                        roleName, "%" + name + "%");
        return users;
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<User> findAccounts(String roleName) {
        List<User> users = getJpaTemplate().find(
                "SELECT DISTINCT p FROM User p WHERE p.role.name != ? AND p.statusCode = 1 ORDER BY p.id", roleName);
        return users;
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<User> findExperts() {
        List<User> users = getJpaTemplate()
                .find("SELECT DISTINCT p FROM User p, IN(p.role) o WHERE o.name = ? AND p.statusCode = 1 ORDER BY o.name, p.id",
                        Constants.EXPERT_ROLE);
        return users;
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<User> findBySchool(School school) {
        return getJpaTemplate().find("SELECT DISTINCT p FROM User p, IN(p.role) o WHERE p.school = ? and o.name = ? ",
                school, Constants.USER_ROLE);
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<User> findBySubSchool(School school) {
        return getJpaTemplate()
                .find("SELECT DISTINCT p FROM User p, IN(p.role) o, School s WHERE s = ? and p.school MEMBER OF s.underSchool and o.name = ? ",
                        school, Constants.USER_ROLE);
    }

    @Override
    @SuppressWarnings("unchecked")
    public User findByStudentId(String studentId, School school) {
        List<User> users = getJpaTemplate()
                .find("SELECT DISTINCT p FROM User p, School s WHERE studentId = ? and s = ? and p.school MEMBER OF s.underSchool ",
                        studentId, school);

        if (!users.isEmpty()) {
            return users.get(0);
        }
        return null;
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<User> findByNameAndSubSchool(String roleName, School school, String name) {
        List<User> users = getJpaTemplate()
                .find("SELECT DISTINCT p FROM User p, IN(p.role) o, School s WHERE o.name = ? AND p.school MEMBER OF s.underSchool AND s = ? AND p.name like ? AND p.statusCode = 1 ORDER BY p.name",
                        roleName, school, "%" + name + "%");
        return users;
    }

    @Override
    public User findByEmail(String email) {
        @SuppressWarnings("unchecked")
        List<User> users = getJpaTemplate().find("FROM User p WHERE email = ?", email);
        if (!users.isEmpty()) {
            return users.get(0);
        }
        return null;
    }

}
